package ru.server;

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import ru.shared.UserStruct;

public class Connect {
	private Connection connect;
	private Statement statement;
	private ResultSet resultSet;
	private String result;
	
	public void initConnection() throws Exception {
		try {
			// This will load the MySQL driver, each DB has its own driver
			// Setup the connection with the DB
			DriverManager.registerDriver(new Driver());
		    Properties properties = new java.util.Properties();
		    properties.put("user", "root");
		    properties.put("password","ghjcnj");
		    properties.put("useUnicode","true");
		    properties.put("characterEncoding","utf8");
		    String databaseURL = "jdbc:mysql://95.172.144.114/LightERP";
			connect = DriverManager.getConnection(databaseURL, properties);

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();
			
		} catch (Exception e) {
			throw e;
		} finally {
			;//close();
		}
	}
	
	public void addUser(UserStruct us) throws Exception{
		String qry = "INSERT INTO `LightERP`.`users` (`user_id` ,	`firstname` ,`lastname` ,`pass` ,`phone`)VALUES (NULL , '";
		qry += us.getNameFirst()+"', '"+us.getNameSecond()+"', '1', '1');";
		statement.executeUpdate(qry);
	
	}

	public String showUsers() throws SQLException{
		String sel = "SELECT * FROM `users`";
		resultSet = statement.executeQuery(sel);
		writeResultSet(resultSet);
		
		return result;
	}

	private void writeResultSet(ResultSet resultSet) throws SQLException {
		String value;
		result = "";
	
		int columnCount = resultSet.getMetaData().getColumnCount();
		String[] columnName = new String[columnCount+1];
		
		result += "Table: " + resultSet.getMetaData().getTableName(1)+"\n";
		for (int i=1; i<=columnCount; i++) {
			columnName[i] = resultSet.getMetaData().getColumnName(i);
			result += columnName[i] +"\t";
		}
		result += "\n";

		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			for (int i=1; i<=columnCount; i++) {
				value = resultSet.getString(columnName[i]);
				result += value +"\t";
			}
			result += "\n";
		}
		//System.out.print(result);
	}

	// You need to close the resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}
